package db

import (
	"errors"
	"fmt"
	"os"
	"strings"

	_ "github.com/go-sql-driver/mysql"
)

//上传图片
func PostImg(path string, title string) (id int64, err error) {
	tx, err := ulwebsiteDb.Begin()
	if err != nil {
		return
	}
	defer tx.Commit()
	rows, err := tx.Exec("insert into imgfile(src,title) values(?,?)", path, title)
	if err != nil {
		tx.Rollback()
		return
	}
	id, err = rows.LastInsertId()
	if err != nil {
		return
	}
	return
}

//删除图片
func DelImg(uid string) (id int64, path string, err error) {
	tx, err := ulwebsiteDb.Begin()
	if err != nil {
		return
	}
	defer tx.Commit()

	//先查询图片的path，然后数据库中删除
	err = ulwebsiteDb.QueryRow(`
	SELECT
		ifnull(src,"") as path
	FROM
		imgfile 
	WHERE
		id = ?
	`, uid).Scan(&path)
	if err != nil {
		err = errors.New("DelImg数据库查询失败")
		return
	}

	rows, err := tx.Exec("DELETE FROM imgfile WHERE id=?", uid)
	if err != nil {
		tx.Rollback()
		return
	}
	id, err = rows.LastInsertId()
	if err != nil {
		return
	}
	return
}

//上传富文本图片
func TextImg(path string, title string) (id int64, err error) {
	tx, err := ulwebsiteDb.Begin()
	if err != nil {
		return
	}
	defer tx.Commit()
	rows, err := tx.Exec("insert into textimgfile(src,title) values(?,?)", path, title)
	if err != nil {
		tx.Rollback()
		return
	}
	id, err = rows.LastInsertId()
	if err != nil {
		return
	}
	return
}

//更新banner图片
func PostBanner(uid string, path string, title string) (id int64, err error) {
	tx, err := ulwebsiteDb.Begin()
	if err != nil {
		return
	}
	defer tx.Commit()

	//根据原path找到文件删除
	var oldPath string
	err = ulwebsiteDb.QueryRow(`
	SELECT
		ifnull(src,"") as path
	FROM
		banner 
	WHERE
		id = ?
	`, uid).Scan(&oldPath)
	if err != nil {
		err = errors.New("PostBanner数据库查询失败")
		return
	}
	if oldPath != "" {
		localPath := oldPath
		err = os.Remove(localPath)
		if err != nil {
			return
		}
	}

	rows, err := tx.Exec("UPDATE banner SET src=?,title=?  WHERE id=?", path, title, uid)
	if err != nil {
		tx.Rollback()
		return
	}
	id, err = rows.LastInsertId()
	if err != nil {
		return
	}
	return
}

//更新banner对应的prodId
func PostBannerProd(idStr string) (id int64, err error) {
	tx, err := ulwebsiteDb.Begin()
	if err != nil {
		return
	}
	defer tx.Commit()

	idArr := strings.Split(idStr, ",")
	if len(idArr) < 6 {
		err = errors.New("PostBannerProd参数错误")
		return
	}

	rows, err := tx.Exec(`
	UPDATE banner SET
		prodId = CASE id
			WHEN 1 THEN ?
			WHEN 2 THEN ?
			WHEN 3 THEN ?
			WHEN 4 THEN ?
			WHEN 5 THEN ?
			WHEN 6 THEN ?
		END
	WHERE id IN (1,2,3,4,5,6)
	`, idArr[0], idArr[1], idArr[2], idArr[3], idArr[4], idArr[5])
	if err != nil {
		tx.Rollback()
		return
	}
	id, err = rows.LastInsertId()
	if err != nil {
		return
	}
	return
}

//获取所有banner
type Banner struct {
	Id      int    `json:"id"`
	Title   string `json:"title"`
	Src     string `json:"src"`
	ProdId  int    `json:"prodId"`
	ProdSrc string `json:"prodSrc"`
}

func GetBanner() (as []Banner, err error) {
	rows, err := ulwebsiteDb.Query(`
	SELECT
	    x.id,ifnull(x.title,""),ifnull(x.src,""),ifnull(x.prodId,0),ifnull(z.src,"") as prodSrc
	FROM
		banner x
	LEFT JOIN
		product y on x.prodId = y.id
	LEFT JOIN
		imgfile z on y.imgId =  z.id
	ORDER BY
		id
	`)
	if err != nil {
		return
	}
	defer rows.Close()
	as = make([]Banner, 0)
	for rows.Next() {
		var a Banner
		err = rows.Scan(&a.Id, &a.Title, &a.Src, &a.ProdId, &a.ProdSrc)
		if err != nil {
			return
		}
		as = append(as, a)
	}
	return
}

type DelUselessImgResult struct {
	Id    int    `json:"id"`
	Src   string `json:"src"`
	Title string `json:"title"`
}

func DelUselessImg() (paths []string, err error) {

	tx, err := ulwebsiteDb.Begin()
	if err != nil {
		return
	}
	defer tx.Commit()

	/* 删除其他图片 */
	//先查询图片的path，然后数据库中删除
	rows, err := tx.Query(`
	SELECT
    *
	FROM
		imgfile i 
	WHERE
		NOT EXISTS ( SELECT a.coverId FROM article a WHERE a.coverId = i.id )
	AND
		NOT EXISTS ( SELECT p.imgId,p.qrcodeId FROM product p WHERE p.imgId = i.id OR p.qrcodeId = i.id)
	`)
	if err != nil {
		tx.Rollback()
		return
	}
	paths = make([]string, 0)
	ids := make([]int, 0)
	for rows.Next() {
		var a DelUselessImgResult
		err = rows.Scan(&a.Id, &a.Src, &a.Title)
		if err != nil {
			return
		}
		paths = append(paths, a.Src)
		ids = append(ids, a.Id)
	}
	if len(ids) != 0 {
		idsStr := strings.Replace(strings.Trim(fmt.Sprint(ids), "[]"), " ", ",", -1)
		_, err = tx.Exec(fmt.Sprintf("DELETE FROM imgfile WHERE id IN (%s)", idsStr))
		if err != nil {
			tx.Rollback()
			return
		}
	}

	/* 删除富文本图片 */
	rows2, err := tx.Query(`
	SELECT
		i.id,
		i.src,
		i.title
	FROM
		textimgfile i
	WHERE
		NOT EXISTS ( SELECT a.textImgs FROM article a WHERE  FIND_IN_SET(i.id,a.textImgs)>0 )
	`)
	if err != nil {
		tx.Rollback()
		return
	}
	ids2 := make([]int, 0)
	for rows2.Next() {
		var a DelUselessImgResult
		err = rows2.Scan(&a.Id, &a.Src, &a.Title)
		if err != nil {
			return
		}
		paths = append(paths, a.Src)
		ids2 = append(ids2, a.Id)
	}
	if len(ids2) != 0 {
		idsStr2 := strings.Replace(strings.Trim(fmt.Sprint(ids2), "[]"), " ", ",", -1)
		_, err = tx.Exec(fmt.Sprintf("DELETE FROM textimgfile WHERE id IN (%s)", idsStr2))
		if err != nil {
			tx.Rollback()
			return
		}
	}

	return
}
